﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NCRGEA.AAL;
using System.Data.SqlClient;
using NCRGEA.Reports;

namespace NCRGEA.Admin
{
    public partial class frmNewMemberImport : Form
    {
        #region Global Variables

        #region Constants

        const int nameField = 70;
        const int addressField = 35;
        const int cityField = 25;
        const int stateField = 2;
        const int zipcodeField = 5;
        const int zipFourField = 4;

        #endregion

        DataTable _memberListing = new DataTable();

        DataTable _importingList = new DataTable();

        DataTable _duplicateList = new DataTable();

        string _finalQuery = string.Empty;

        #endregion

        #region Data Manipulation

        private void FormatDataTable()
        {
            _memberListing.Columns.Add("FirstName");
            _memberListing.Columns.Add("LastName");
            _memberListing.Columns.Add("MiddleName");
            _memberListing.Columns.Add("Address");
            _memberListing.Columns.Add("City");
            _memberListing.Columns.Add("ZipCode");
            _memberListing.Columns.Add("ZipFour");
            _memberListing.Columns.Add("State");
            _memberListing.Columns.Add("Retirement Date");
            
        }

        /// <summary>
        /// Obtain the pieces of the string for splitting
        /// </summary>
        /// <param name="master"></param>
        private bool ObtainStringData(string master)
        {
            Logger.Log("Splitting file line: " + master);
            try
            {
                int aLength = nameField;
                int cLength = nameField + addressField;
                int sLength = nameField + addressField + cityField;
                int zLength = nameField + addressField  + cityField + stateField;
                int z4Length = nameField + addressField + cityField + stateField + zipcodeField;

                string name = master.Substring(0,nameField);
                string address = master.Substring(aLength, addressField).Trim();
                string city = master.Substring(cLength, cityField).Trim();
                string state = master.Substring(sLength, stateField).Trim();
                string zipCode = master.Substring(zLength,zipcodeField).Trim();
                string zipFour = master.Substring(z4Length, zipFourField).Trim();

                Logger.Log("Splitting the first name.");

                name = name.Trim();

                string[] split = name.Split(',');

                /* Expected results
                 * string a = ANDRA, BOB C
                 * string[] split = a.split(',')
                 * 
                 * firstName = split[0];
                 * 
                 * string split2 = split[1].split(' ');
                 * 
                 * string lastName = split2[0];
                 * string middleName = split2[1];
                 */
            
                string lastName = split[0];
                string[] split2 = split[1].Trim().Split(' ');

                string firstName = split2[0].Trim();
                string middleName = string.Empty;

                if (split2.Length > 1)
                {
                    middleName = split2[1].Trim();
                }

                DateTime retirementDate = DateTime.Today;
                string retirementString = retirementDate.Month + "/" + retirementDate.Day + "/" + retirementDate.Year;

                _memberListing.Rows.Add(firstName, lastName, middleName, address, city, zipCode, zipFour, state, retirementString);
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show("There was an error while attempting to import memebers");
                Logger.Log("There was an error while attempting to import new members.");
                return false;
            }
        }

        /*
        /// <summary>
        /// Splits the name into the needed pieces, first name, last name and middle name.
        /// </summary>
        /// <param name="name"></param>
        private void SplitName(string name)
        {
            Logger.Log("Splitting the first name.");

            name = name.Trim();

            string[] split = name.Split(',');

            /* Expected results
             * string a = RUDA, COREY C
             * string[] split = a.split(',')
             * 
             * firstName = split[0];
             * 
             * string split2 = split[1].split(' ');
             * 
             * string lastName = split2[0];
             * string middleName = split2[1];
             */

            /*string firstName = split[0];
            string[] split2 = split[1].Split(' ');

            string lastName = split2[0];
            string middleName = split2[1];
        }*/

        private void ChooseFile()
        {
            DialogResult result = odDialog1.ShowDialog();
            odDialog1.Filter = "Text Files|*.txt";
            string line = string.Empty;

            try
            {
                switch(result)
                {
                    case DialogResult.Cancel:
                        return;
                }

                System.IO.StreamReader sr = new System.IO.StreamReader(odDialog1.FileName);
                
                while ((line = sr.ReadLine()) != null)
                {
                    if (!ObtainStringData(line))
                        break;
                }

                sr.Close();
            }
            catch(FileNotFoundException e)
            {
                Logger.Log("User did not select a file. " + e.Message);
                return;
            }

            Logger.Log("File selection completed.");

            grdImportMembers.DataSource = null;
            grdImportMembers.DataSource = _memberListing;

            if (_memberListing.Rows.Count > 0 && _memberListing != null)
            {
                btnImport.Enabled = true;
                btnPrintDuplicate.Enabled = true;
                btnPrintList.Enabled = true;
                
                foreach (DataGridViewRow d in grdImportMembers.Rows)
                    d.Cells[0].Value = 1;
            }
        }

        #endregion

        public frmNewMemberImport()
        {
            InitializeComponent();
            FormatDataTable();
        }

        private void btnChooseFile_Click(object sender, EventArgs e)
        {
            ChooseFile();
        }

        private void btnAll_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow d in grdImportMembers.Rows)
                d.Cells[0].Value = 1;
        }

        private void btnNone_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow d in grdImportMembers.Rows)
                d.Cells[0].Value = 0;

        }

        private void ObtainDuplicateReport()
        {
            
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            string fname, lname, mname, address, zipcode, zipfour,state,city, retirementString;
            DateTime retirementDate;


            foreach (DataGridViewRow gRow in grdImportMembers.Rows)
            {
                string boolParse = gRow.Cells[0].Value.ToString();
                int isChecked = int.Parse(boolParse);
                if (isChecked != 0)
                {
                    fname = gRow.Cells["gcolFirstName"].Value.ToString();
                    lname = gRow.Cells["gcolLastName"].Value.ToString();
                    mname = gRow.Cells["gcolMiddleName"].Value.ToString();
                    address = gRow.Cells["gcolAddress"].Value.ToString();
                    zipcode = gRow.Cells["gcolZipcode"].Value.ToString();
                    zipfour = gRow.Cells["gcolZipfour"].Value.ToString();
                    state = gRow.Cells["gcolState"].Value.ToString();
                    city = gRow.Cells["gcolCity"].Value.ToString();
                    retirementDate = DateTime.Parse(gRow.Cells["gcolRetirementDate"].Value.ToString());

                    retirementString = retirementDate.Month + "/" + retirementDate.Day + "/" + retirementDate.Year;

                    //DateTime today = DateTime.Today;

                    //string date = today.Month + "/" + today.Day + "/" + today.Year;

                    //gRow.Cells[0].Value = 1;

                    gRow.Cells["gcolRetirementDate"].Value = retirementDate;
                    
                    if (DetermieDuplicate(fname, lname, mname, address, state, zipcode, zipfour, city, retirementString))
                    {
                        _duplicateList.Rows.Add(fname, lname, mname, address, state, zipfour, city, retirementString);
                    }
                    SaveNewMember(fname, lname, mname, address, state, zipcode, zipfour, city, retirementString);
                }
            }

            MessageBox.Show("New member import complete.", "New Member Import");
        }


        /// <summary>
        /// Determines if there is a duplicate member inside of the database
        /// </summary>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="address"></param>
        /// <param name="state"></param>
        /// <param name="zipcode"></param>
        /// <param name="zipfour"></param>
        private bool DetermieDuplicate(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city, string pRetirementDate)
        {
            string query = @"SELECT
                                *
                             FROM
                                memInfo
                            WHERE
                                (FirstName = @FirstName AND LastName = @LastName) OR (Address1 = @Address AND State = @State AND (City = @City OR Zip = @Zipcode OR ZipFour = @Zipfour))";

            SqlParameter[] param = new SqlParameter[7];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3]= new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@City", city);

            DataTable exist = DAL.ExecuteQuery(query, param);

            if (exist.Rows.Count > 0 && exist != null)
            {
                string message = "A member already exisits with similar information. \n" + fname + " " + mname + " " + lname + " " + address + " " + zipfour;

                if (zipfour != string.Empty)
                    message = message + "-" + zipfour;
                /*
                message = message + "\nDo you wish to update?";

                switch (MessageBox.Show(message, "Duplicate Member", MessageBoxButtons.YesNo))
                {
                    case DialogResult.Yes:
                        UpdateCurrentMember(fname, lname, mname, address, state, zipcode, zipfour,city);
                        break;
                    case DialogResult.No:
                        SaveNewMember(fname, lname, mname, address, state, zipcode, zipfour, city);
                        break;
                }
                */
                return true;
            }
            else
            {
                //SaveNewMember(fname, lname, mname, address, state, zipcode, zipfour,city,pRetirementDate);
                return false;
            }

            return false;
        }

        /// <summary>
        /// Creates the insert string to insert the mebmer into the database
        /// </summary>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="address"></param>
        /// <param name="state"></param>
        /// <param name="zipcode"></param>
        /// <param name="zipfour"></param>
        private void SaveNewMember(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city, string pRetirementDate)
        {
            string query = @"INSERT INTO
                                memInfo (FirstName, MiddleName, LastName, Address1, State, Zip, Zipfour, TypeID, City, RetirementDate)
                            VALUES
                                (@FirstName, @MiddleName, @LastName,@Address,@State, @Zipcode,@Zipfour,2,@City, @RetirementDate)";

            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3]= new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@MiddleName", mname);
            param[7] = new SqlParameter("@City", city);
            param[8] = new SqlParameter("@RetirementDate", pRetirementDate);

            DAL.ExecuteNonQuery(query, param);
        }

        private void UpdateCurrentMember(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city)
        {
            string query = @"UPDATE
                                memInfo 
                            SET
                                FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Address1 = @Address, State = @State, 
                                Zip = @Zipcode, Zipfour = Zipfour
                            WHERE
                                FirstName = @FirstName AND LastName= @LastName";

            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3] = new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@MiddleName", mname);
            param[7] = new SqlParameter("@City", city);

            DAL.ExecuteNonQuery(query, param);
        }

        private void btnPrintList_Click(object sender, EventArgs e)
        {
            DataTable importList = new DataTable();

            importList.Columns.Add("FirstName");
            importList.Columns.Add("LastName");
            importList.Columns.Add("MiddleName");
            importList.Columns.Add("Address");
            importList.Columns.Add("City");
            importList.Columns.Add("State");
            importList.Columns.Add("Zipcode");
            importList.Columns.Add("Zipfour");
            importList.Columns.Add("RetirementDate");

            foreach (DataGridViewRow row in grdImportMembers.Rows)
            {
                importList.Rows.Add(row);
            }

            if (importList != null && importList.Rows.Count > 0)
            {
                frmDynamicReport fDynReport = new frmDynamicReport(importList);
                fDynReport.ShowDialog();
            }
        }
    }
}
